﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyThuVien.DTO;
using System.Data;

namespace QuanLyThuVien.DAO
{
    class SoGhiNoDAO
    {
        //ha phuong
        public static void ThemPhieuNo(SoGhiNoDTO x)
        {
            NorthwindDataContext QLTV = new NorthwindDataContext();
            SoGhiNo p = new SoGhiNo();
            p.TongNo = x.TongNo1;
            p.MaDocGia = x.MaDocGia1;
            QLTV.SoGhiNos.InsertOnSubmit(p);
            QLTV.SubmitChanges();
        }
        public static void SuaPhieuNo(SoGhiNoDTO sa)
        {
            NorthwindDataContext QLTV = new NorthwindDataContext();
            SoGhiNo s = QLTV.SoGhiNos.Single(i => i.MaSoGhiNo == sa.MaSoGhiNo1);
            s.TongNo = sa.TongNo1;
            QLTV.SubmitChanges();
        }
        public static double LayTongNo(int MaDG)
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var tongno = (from t in cnn.SoGhiNos
                        where t.MaDocGia == MaDG
                        select t.TongNo).Max();
            return tongno;
        }

        public static DataTable ThongTinSoNo()
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sql = from sn in cnn.SoGhiNos
                       select new { sn.MaSoGhiNo, sn.MaDocGia };
            DataTable dt = new DataTable();
            dt.Columns.Add("MaSoGhiNo",typeof(int));
            dt.Columns.Add("MaDocGia", typeof(int));
            foreach (var gan in sql)
            {
                dt.Rows.Add(gan.MaSoGhiNo,gan.MaDocGia);
            }
            return dt;
        }
        //lấy ra sô nơ cua 1 độc giả

        public static DataTable DocGiaNo(int MaDG)
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sql = from d in cnn.DocGias
                      join s in cnn.SoGhiNos on d.MaDocGia equals s.MaDocGia
                      where d.MaDocGia == MaDG
                      select new { d.HoTenDocGia, s.TongNo };
            DataTable dt = new DataTable();
            dt.Columns.Add("HoTenDocGia",typeof(string));
            dt.Columns.Add("TongNo", typeof(int));
            foreach (var gan in sql)
            {
                dt.Rows.Add(gan.HoTenDocGia,gan.TongNo);
            }
            return dt;
        }

        public static DataTable LayTongNoVaMaSoGhiNo(int MaDG)
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sql = from s in cnn.SoGhiNos
                      where s.MaDocGia == MaDG
                      select new { s.TongNo, s.MaSoGhiNo };

            DataTable dt = new DataTable();
            dt.Columns.Add("TongNo",typeof(int));
            dt.Columns.Add("MaSoGhiNo", typeof(int));
            foreach (var gan in sql)
            {
                dt.Rows.Add(gan.TongNo, gan.MaSoGhiNo);
            }
            return dt;
        }

        //truyen
        //public static DataTable TienNo()
        //{
        //    NorthwindDataContext db = new NorthwindDataContext();
        //    var tienNo = from soNo in db.SoGhiNos
        //                 join dg in db.DocGias on soNo.MaDocGia equals dg.MaDocGia
        //                 select new
        //                 {
        //                     dg.HoTenDocGia,
        //                     soNo.TongNo,
        //                 };
        //    DataTable dt = new DataTable();
        //    dt.Columns.Add("Tên Độc Giả", typeof(string));
        //    dt.Columns.Add("Tiền Nợ", typeof(float));
        //    foreach (var gtri in tienNo)
        //    {
        //        dt.Rows.Add(gtri.HoTenDocGia, gtri.TongNo);
        //    }

        //    return dt;
        //}
    }
}
